﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using HPIT_Library;
using HPIT_Library_Modal;

namespace HPIT_Library_DAL
{
   public class userSerives
    {
        //使用存储过程进行模糊查询
        public static List<member> SelectProc( string MemberName, string MemeberPhone)
        {
            string procName = "User_Select";
            SqlParameter[] pams = { 
            new SqlParameter("@MemberName",MemberName==null?"":MemberName),
             new SqlParameter("@MemberPhone",MemeberPhone==null?"":MemeberPhone),
            };
          DataTable dt=  DbHelper.ExecuteTableProc(procName, pams);

            List<member> members = new List<member>();
            if (dt.Rows.Count>0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    members.Add(new member() { 
                        id=Convert.ToInt32(row["id"]),
                        MemberName=row["MemberName"].ToString(),
                        MemberPhone= row["MemberPhone"].ToString(),
                        MemberSex =Convert.ToInt32( row["MemberSex"]),
                        MemberSite = row["MemberSite"].ToString(),
                        MemberTime=Convert.ToDateTime(row["MemberTime"])
                        
                    });
                }
                return members;
            }
            return null;
        }

        public static DataTable Select(string MemberName="",string MemberPhone="")
        {
            string sql = "select * from member where 1=1";
            if (MemberName!="")
            {
                sql += " and MemberName like @MemberName";
            }
            if (MemberPhone!="")
            {
                sql += " and MemberPhone like @MemberPhone";
            }
            SqlParameter[] pams = { 
                new SqlParameter("@MemberName","%"+MemberName+"%"),
                 new SqlParameter("@MemberPhone","%"+MemberPhone+"%"),

            };
            return DbHelper.ExecuteTable(sql, pams);
        }
        public static member SelectID(int id)
        {
            string sql = "select * from [member] where id=@id";
            SqlParameter sqlParameter = new SqlParameter("@id", id);

            SqlDataReader reader = DbHelper.ExecuteReader(sql, sqlParameter);
            member user = null;
            if (reader.Read())
            {
                user = new member()
                {
                    id = Convert.ToInt32(reader["id"]),
                    MemberName = Convert.ToString(reader["MemberName"]),
                    MemberSex = Convert.ToInt32(reader["MemberSex"]),
                    MemberPhone=Convert.ToString(reader["MemberPhone"]),
                    MemberSite= Convert.ToString(reader["MemberSite"]),
                   
                };
            }
            reader.Close();
            return user;
        }
        public static int Update(member member)
        {
            string sql = "update member set MemberName=@MemberName,MemberSex=@MemberSex,MemberPhone=@MemberPhone,MemberSite=@MemberSite where id=@id";
            SqlParameter[] pams = {
            new SqlParameter("@MemberName",member.MemberName),
            new SqlParameter("@MemberSex",member.MemberSex),
            new SqlParameter("@MemberPhone",member.MemberPhone),
            new SqlParameter("@MemberSite",member.MemberSite),
            new SqlParameter("@id",member.id),
            };
            return DbHelper.ExecuteNonQuery(sql, pams);
        }

        public static int AddUser(member member)
        {
            //接受存储过程名称
            string procName = "User_Insert";
            SqlParameter[] pams = 
                {
                new SqlParameter("@MemberName",member.MemberName),
                new SqlParameter("@MemberSex",member.MemberSex),
                new SqlParameter("@MemberSite",member.MemberSite),
                new SqlParameter("@MemberPhone",member.MemberPhone)
            };
            return DbHelper.ExecuteNonQueryProc(procName, pams);
        }
        public static List<member> GetSelectProc(MemberModel serchModel,int PageIndex,int PageSize,ref int TotalCount)
        {
            string ProcName = "User_Insert";
            SqlParameter[] pams = {
                new SqlParameter("@MemberName",serchModel.MemberName),

                new SqlParameter("@PageIndex",PageIndex),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@TotalCount",TotalCount),
            };
            pams[3].Direction = ParameterDirection.Output;
            SqlDataReader reader = DbHelper.ExecuteReaderProc(ProcName, pams);
            List<member> list = new List<member>();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    member member = new member();
                    member.id = Convert.ToInt32(reader["id"]);
                    member.MemberName = Convert.ToString(reader["MemberName"]);
                    member.MemberSite = Convert.ToString(reader["MemberSite"]);
                    member.MemberSex = Convert.ToInt32(reader["MemberSex"]);
                    member.MemberPhone = Convert.ToString(reader["MemberPhone"]);
                    member.MemberTime = Convert.ToDateTime(reader["MemberTime"]);
                    list.Add(member);
                }
                reader.Close();
                TotalCount = Convert.ToInt32(pams[3].Value);
            }
            return list;
        }
    }
}
